IF EXISTS ( SELECT 'X' FROM sys.views WHERE name = 'vPackageExecutionLog' AND schema_id = 1 )
BEGIN
     DROP VIEW dbo.vPackageExecutionLog
END
GO

CREATE VIEW dbo.vPackageExecutionLog
AS
/*
 *
 * vPackageExecutionLog
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Calling
 * Application:  
 *
 * Note(s):      
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 05/30/2009, 1.00.0000
 *
 */
SELECT PackageExecutionId       = MiscBigInt1,
       PackageExecutionDt       = LogDt,
       PackageName              = LogByDesc,
       PackageGUID              = MiscGUID1,
       ExecutionGUID            = MiscGUID4,
       PackageMajorVersion      = MiscInt1,
       PackageMinorVersion      = MiscInt2,
       PackageBuildVersion      = MiscInt3,
       MachineName              = MiscVC1,
       UserName                 = MiscVC2,
       ContainerStartTime       = MiscDt1,
       PackageStartTime         = MiscDt2,
       PackageEndTime           = MiscDt3,
       ParentPackageExecutionId = MiscBigInt2,
       PackageStateId           = MiscTinyInt1,
       PackageStateDesc         = CONVERT( varchar(10), CASE WHEN MiscTinyint1 = 1
                                                             THEN 'Start'
                                                             WHEN MiscTinyint1 = 128
                                                             THEN 'Info'
                                                             WHEN MiscTinyint1 = 255
                                                             THEN 'End'
                                                             ELSE 'Unknown'
                                                        END )
FROM   dbo.EventLog 
WHERE  LogByApp          = 'Integration Services'
AND    LogByAppSubSystem = 'InsPackageExecutionLog'
GO
